using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Musicman.Entities;
using Musicman.Common;
using Musicman.Entities.BusinessEntities;
namespace Musicman.DAL
{
	public static class SearchfileDAO
	{
		#region Insert
		private static DbCommand insertCommand = null;
        private static string insertSQL = "INSERT INTO SearchFile (SEARCHFOLDERID, NAME, CHANGETIME, LENGTH, SEARCHSEQUENCE) VALUES (@SEARCHFOLDERID, @NAME, @CHANGETIME, @LENGTH, @SEARCHSEQUENCE) SET @SEARCHFILEID = SCOPE_IDENTITY()";
		private static DbCommand GetInsertCommand(SqlDatabase db)
		{
			if (insertCommand == null)
			{
				insertCommand = db.GetSqlStringCommand(insertSQL);
				db.AddOutParameter(insertCommand, "@SEARCHFILEID", DbType.Int64, 8);
                db.AddInParameter(insertCommand, "@SEARCHFOLDERID", DbType.Int64);
				db.AddInParameter(insertCommand, "@NAME", DbType.String);
				db.AddInParameter(insertCommand, "@CHANGETIME", DbType.DateTime);
                db.AddInParameter(insertCommand, "@LENGTH", DbType.Int64);
                db.AddInParameter(insertCommand, "@SEARCHSEQUENCE", DbType.Int64);

			}
			return insertCommand;
		}
		private static void SetInsertValues(SqlDatabase db, DbCommand cmd, Searchfile entity)
		{
            db.SetParameterValue(cmd, "@SEARCHFOLDERID", entity.Searchfolderid);
			db.SetParameterValue(cmd, "@NAME", entity.Name);
			db.SetParameterValue(cmd, "@CHANGETIME", entity.Changetime);
            db.SetParameterValue(cmd, "@LENGTH", entity.Length);
            db.SetParameterValue(cmd, "@SEARCHSEQUENCE", entity.SearchSequence);

		}
		public static bool InsertSearchfile(Searchfile entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
			DbCommand cmd = GetInsertCommand(db);
			SetInsertValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			if (res)
			{
				entity.Searchfileid = (long?)db.GetParameterValue(cmd, "@SEARCHFILEID");
			}
			return res;
		}
		public static bool InsertSearchfile(Searchfile entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = false;
			DbCommand cmd = GetInsertCommand(db);
			SetInsertValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			if (res)
			{
				entity.Searchfileid = (long?)db.GetParameterValue(cmd, "@SEARCHFILEID");
			}
			return res;
		}
		public static bool InsertListSearchfile(List<Searchfile> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				DbCommand cmd = GetInsertCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Searchfile entity = list[i];
					SetInsertValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
					if (res)
					{
						entity.Searchfileid = (long?)db.GetParameterValue(cmd, "@SEARCHFILEID");
					}
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool InsertListSearchfile(List<Searchfile> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			DbCommand cmd = GetInsertCommand(db);
			for(int i=0; i<list.Count; i++)
			{
				Searchfile entity = list[i];
				SetInsertValues(db, cmd, entity);
				res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				if (res)
				{
					entity.Searchfileid = (long?)db.GetParameterValue(cmd, "@SEARCHFILEID");
				}
			}
			return res;
		}
		#endregion

		#region Update
		private static DbCommand updateCommand = null;
        private static string updateSQL = "UPDATE SearchFile SET SEARCHFOLDERID = @SEARCHFOLDERID, NAME = @NAME, CHANGETIME = @CHANGETIME, LENGTH = @LENGTH, SEARCHSEQUENCE = @SEARCHSEQUENCE WHERE SEARCHFILEID = @SEARCHFILEID";
		private static DbCommand GetUpdateCommand(SqlDatabase db)
		{
			if (updateCommand == null)
			{
				updateCommand = db.GetSqlStringCommand(updateSQL);
				db.AddInParameter(updateCommand, "@SEARCHFILEID", DbType.Int64);
                db.AddInParameter(updateCommand, "@SEARCHFOLDERID", DbType.Int64);
				db.AddInParameter(updateCommand, "@NAME", DbType.String);
				db.AddInParameter(updateCommand, "@CHANGETIME", DbType.DateTime);
                db.AddInParameter(updateCommand, "@LENGTH", DbType.Int64);
                db.AddInParameter(updateCommand, "@SEARCHSEQUENCE", DbType.Int64);

			}
			return updateCommand;
		}
		private static void SetUpdateValues(SqlDatabase db, DbCommand cmd, Searchfile entity)
		{
			db.SetParameterValue(cmd, "@SEARCHFILEID", entity.Searchfileid);
            db.SetParameterValue(cmd, "@SEARCHFOLDERID", entity.Searchfolderid);
			db.SetParameterValue(cmd, "@NAME", entity.Name);
			db.SetParameterValue(cmd, "@CHANGETIME", entity.Changetime);
            db.SetParameterValue(cmd, "@LENGTH", entity.Length);
            db.SetParameterValue(cmd, "@SEARCHSEQUENCE", entity.SearchSequence);

		}
		public static bool UpdateSearchfile(Searchfile entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
			DbCommand cmd = GetUpdateCommand(db);
			SetUpdateValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			return res;
		}
		public static bool UpdateSearchfile(Searchfile entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = false;
			DbCommand cmd = GetUpdateCommand(db);
			SetUpdateValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			return res;
		}
		public static bool UpdateListSearchfile(List<Searchfile> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				DbCommand cmd = GetUpdateCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Searchfile entity = list[i];
					SetUpdateValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool UpdateListSearchfile(List<Searchfile> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			DbCommand cmd = GetUpdateCommand(db);
			for(int i=0; i<list.Count; i++)
			{
				Searchfile entity = list[i];
				SetUpdateValues(db, cmd, entity);
				res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			}
			return res;
		}
		
		#endregion

		#region Save
		public static bool SaveSearchfile(Searchfile entity)
		{
			bool res = true;
			if (entity.Searchfileid == null )
			{
				res = InsertSearchfile(entity);
			}
			else
			{
				res = UpdateSearchfile(entity);
			}
			return res;
		}
		public static bool SaveSearchfile(Searchfile entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			if (entity.Searchfileid == null )
			{
				res = InsertSearchfile(entity, transactionObj, db);
			}
			else
			{
				res = UpdateSearchfile(entity, transactionObj, db);
			}
			return res;
		}
		public static bool SaveListSearchfile(List<Searchfile> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Searchfile entity = list[i];
					res = res & SaveSearchfile(entity, transactionObj, db);
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool SaveListSearchfile(List<Searchfile> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			for(int i=0; i<list.Count; i++)
			{
				Searchfile entity = list[i];
				res = res & SaveSearchfile(entity, transactionObj, db);
			}
			return res;
		}
		#endregion

		#region Delete
		private static DbCommand deleteCommand = null;
        private static string deleteProcName = "dbo.DeleteSearchFile";
		private static DbCommand GetDeleteCommand(SqlDatabase db)
		{
			if (deleteCommand == null)
			{
                deleteCommand = db.GetStoredProcCommand(deleteProcName);
				db.AddInParameter(deleteCommand, "@SEARCHFILEID", DbType.Int64);
			}
			return deleteCommand;
		}
		private static void SetDeleteValues(SqlDatabase db, DbCommand cmd, Searchfile entity)
		{
			db.SetParameterValue(cmd, "@SEARCHFILEID", entity.Searchfileid);
		}
		public static bool DeleteSearchfile(Searchfile entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
			DbCommand cmd = GetDeleteCommand(db);
			SetDeleteValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			return res;
		}
		public static bool DeleteListSearchfile(List<Searchfile> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				DbCommand cmd = GetDeleteCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Searchfile entity = list[i];
					SetDeleteValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		
		#endregion

		#region Select
        private static string selectSQL = "SELECT SEARCHFILEID,SEARCHFOLDERID,NAME,CHANGETIME,LENGTH, SEARCHSEQUENCE FROM SearchFile {0} {1}";
		
		public static List<Searchfile> LoadSearchfiles(string where, string orderby)
		{
			List<Searchfile> res = new List<Searchfile>();
			string sql = String.Format(selectSQL, where, orderby);
			IDataReader reader = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				reader = db.ExecuteReader(CommandType.Text, sql);
				if (reader != null)
				{
					while (reader.Read())
					{
						Searchfile entity = new Searchfile();
						entity.Searchfileid = reader.IsDBNull(0) ? null : (long?)reader.GetInt64(0);
                        entity.Searchfolderid = reader.IsDBNull(1) ? null : (long?)reader.GetInt64(1);
						entity.Name = reader.IsDBNull(2) ? null : (string)reader.GetString(2);
						entity.Changetime = reader.IsDBNull(3) ? null : (DateTime?)reader.GetDateTime(3);
                        entity.Length = reader.IsDBNull(4) ? 0: reader.GetInt64(4);
                        entity.SearchSequence = reader.GetInt64(5);
						res.Add(entity);
					}
				}
				reader.Close();
			}
			catch (Exception ex)
			{
				if (reader != null && !reader.IsClosed)
				{
					reader.Close();
				}
				res = null;
				throw ex;
			}
			return res;
		}

        public static List<SearchResult> SearchFiles(string top, string where, string orderby)
        {
            string select = String.Format(@"
                                    select {0} filee.SearchFileID, filee.name, filee.length, (folder.name + '\' + filee.name) as path, location.name from dbo.SearchJob job
                                        join dbo.SearchLocation location on job.SearchJobID = location.SearchJobID
	                                    join dbo.SearchFolder folder on folder.SearchLocationID = location.SearchLocationID
                                        join dbo.SearchFile filee on filee.SearchFolderID = folder.SearchFolderID
	                                {1} {2} ", top, where, orderby);

            List<SearchResult> res = new List<SearchResult>();
			IDataReader reader = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                reader = db.ExecuteReader(CommandType.Text, select);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        SearchResult entity = new SearchResult();
                        entity.FileID = reader.GetInt64(0);
                        entity.Name = reader.IsDBNull(1) ? String.Empty : reader.GetString(1);
                        entity.Length = reader.IsDBNull(2) ? 0 : reader.GetInt64(2);
                        entity.Fullpath = reader.IsDBNull(3) ? String.Empty : reader.GetString(3);
                        entity.Location = reader.IsDBNull(4) ? String.Empty : reader.GetString(4);
                        res.Add(entity);
                    }
                }
                reader.Close();
			}
			catch (Exception ex)
			{
				if (reader != null && !reader.IsClosed)
				{
					reader.Close();
				}
				res = null;
				throw ex;
			}
			return res;
        }

        public static List<SearchResult> GetPendingDownloads(string where, string orderby)
        {
            string select = String.Format(@"
                                    select filee.SearchFileID, filee.name, filee.length, (folder.name + '\' + filee.name) as path, location.name from UserDownloads downloads 
                                        join SearchFile filee on downloads.SearchFielID = filee.SearchFileID
                                        join SearchFolder folder on folder.SearchFolderID = filee.SearchFolderID
                                        join SearchLocation location on location.SearchLocationID = folder.SearchLocationID
	                                {0} {1} ", where, orderby);

            List<SearchResult> res = new List<SearchResult>();
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                reader = db.ExecuteReader(CommandType.Text, select);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        SearchResult entity = new SearchResult();
                        entity.FileID = reader.GetInt64(0);
                        entity.Name = reader.IsDBNull(1) ? String.Empty : reader.GetString(1);
                        entity.Length = reader.IsDBNull(2) ? 0 : reader.GetInt64(2);
                        entity.Fullpath = reader.IsDBNull(3) ? String.Empty : reader.GetString(3);
                        entity.Location = reader.IsDBNull(4) ? String.Empty : reader.GetString(4);
                        res.Add(entity);
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public static List<SearchResult> GetTopDownloaded(string top, string where)
        {
            string select = String.Format(@"
                    select {0} filee.SearchFileID, filee.name, filee.length, (folder.name + '\' + filee.name) as path, location.name from dbo.filesStats stats
                                        join dbo.SearchFile filee on stats.SearchFileID = filee.SearchFileID
                                        join dbo.SearchFolder folder on folder.SearchFolderID = filee.SearchFolderID
                                        join dbo.SearchLocation location on location.SearchLocationID = folder.SearchLocationID 
                                        join dbo.SearchJob job on location.SearchJobID = job.SearchJobID {1}
										order by stats.totaldownloads desc ", top, where);

            List<SearchResult> res = new List<SearchResult>();
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                reader = db.ExecuteReader(CommandType.Text, select);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        SearchResult entity = new SearchResult();
                        entity.FileID = reader.GetInt64(0);
                        entity.Name = reader.IsDBNull(1) ? String.Empty : reader.GetString(1);
                        entity.Length = reader.IsDBNull(2) ? 0 : reader.GetInt64(2);
                        entity.Fullpath = reader.IsDBNull(3) ? String.Empty : reader.GetString(3);
                        entity.Location = reader.IsDBNull(4) ? String.Empty : reader.GetString(4);
                        res.Add(entity);
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public static List<SearchResult> GetTopRated(string top, string where)
        {
            string select = String.Format(@"
                    select {0} filee.SearchFileID, filee.name, filee.length, (folder.name + '\' + filee.name) as path, location.name from dbo.filesStats stats
                                        join dbo.SearchFile filee on stats.SearchFileID = filee.SearchFileID
                                        join dbo.SearchFolder folder on folder.SearchFolderID = filee.SearchFolderID
                                        join dbo.SearchLocation location on location.SearchLocationID = folder.SearchLocationID 
                                        join dbo.SearchJob job on location.SearchJobID = job.SearchJobID {1}
										order by stats.rating desc ", top, where);

            List<SearchResult> res = new List<SearchResult>();
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                reader = db.ExecuteReader(CommandType.Text, select);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        SearchResult entity = new SearchResult();
                        entity.FileID = reader.GetInt64(0);
                        entity.Name = reader.IsDBNull(1) ? String.Empty : reader.GetString(1);
                        entity.Length = reader.IsDBNull(2) ? 0 : reader.GetInt64(2);
                        entity.Fullpath = reader.IsDBNull(3) ? String.Empty : reader.GetString(3);
                        entity.Location = reader.IsDBNull(4) ? String.Empty : reader.GetString(4);
                        res.Add(entity);
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public static List<SearchResult> SearchFilesRandom(string where, string orderby, int top)
        {
            string select = String.Format(@"
                                    select top {0} filee.SearchFileID, filee.name, filee.length, (folder.name + '\' + filee.name) as path, location.name from SearchJob job
		                                join SearchLocation location on job.SearchJobID = location.SearchJobID
		                                join SearchFolder folder on location.SearchLocationID = folder.SearchLocationID
		                                join SearchFile filee on folder.SearchFolderID = filee.SearchFolderID
	                                {1} {2} ", top, where, orderby);

            List<SearchResult> res = new List<SearchResult>();
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                reader = db.ExecuteReader(CommandType.Text, select);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        SearchResult entity = new SearchResult();
                        entity.FileID = reader.GetInt64(0);
                        entity.Name = reader.IsDBNull(1) ? String.Empty : reader.GetString(1);
                        entity.Length = reader.IsDBNull(2) ? 0 : reader.GetInt64(2);
                        entity.Fullpath = reader.IsDBNull(3) ? String.Empty : reader.GetString(3);
                        entity.Location = reader.IsDBNull(4) ? String.Empty : reader.GetString(4);
                        res.Add(entity);
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public static long GetMaxSearchSequence(long jobID)
        {
            long res = 0;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            string sql = String.Format(@"select isnull(max(sf.searchsequence), 0) from dbo.searchfile sf 
join dbo.searchfolder sfol on sfol.searchfolderid = sf.searchfolderid
join dbo.SearchLocation sloc on sloc.searchlocationid = sfol.searchlocationid
join dbo.SearchJob job on job.searchjobid = sloc.searchjobid where job.searchjobid = {0}", jobID);
            res = (long)db.ExecuteScalar(CommandType.Text, sql);
            return res;
        }

		#endregion

	}
}
